﻿using EasyDater.Helper;
using System.ComponentModel;
using System.Data;
using System.Collections;
namespace EasyDater.Base
{
    public static class TableInfo
    {

        //获取实体类T 字段（fieldName）上的Description
        public static string? GetFieldDescription<T>(string fieldName)
        {
            Type clsType = typeof(T);
            PropertyDescriptor? pd = TypeDescriptor.GetProperties(clsType)[fieldName];
            DescriptionAttribute? description = pd == null ? null : pd.Attributes[typeof(DescriptionAttribute)] as DescriptionAttribute;

            if (description is null) return null;
            return description.Description;

        }
        //获取数据库中所有表
        public static ArrayList GetTableList()
        {
            ArrayList arrayList = new ArrayList();
            string strSQL = "select [Name] from sysobjects where xtype='u' and status>=0";
            DataTable dt = SQLHelper.ExecuteSqlGetDataSet(strSQL).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                arrayList.Add(dr[0].ToString());
            }
            return arrayList;

        }

        /// <summary>
        /// 获取表的结构
        /// 包含4列  列名，数据类型，长度，是否主键
        /// </summary>
        /// <param name="tableName">表的名字</param>
        /// <returns>4列的DataTable</returns>
        public static DataTable GetTableStruct(string tableName)
        {
            return SQLHelper.ExecuteSqlGetDataSet(GetColsSQL(tableName)).Tables[0];

        }

       

        /// <summary>
        /// 组织SQL语句
        /// </summary>
        /// <param name="tableName">表的名字</param>
        /// <returns>列名,列类型，类型的长度，是否为主键，是否可以为空(0非空)</returns>
        public static string GetColsSQL(string tableName)
        {
            string strSQL = @"SELECT syscolumns.Name as col_name, SysTypes.Name as col_type, syscolumns.Length as col_len, isnull(sys.extended_properties.Value,syscolumns.Name) as col_memo,
case when syscolumns.name in 
(select primarykey=a.name
FROM syscolumns a
 inner join sysobjects b on a.id=b.id  and b.xtype='U' and b.name<>'dtproperties'
 where  exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
 SELECT name FROM sysindexes WHERE indid in(
  SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid and status=2066
 ))) 
and b.name=sysobjects.Name 
)
 then 1 else 0 end as is_key,syscolumns.isnullable as col_null
 FROM sysobjects,SysTypes,syscolumns 
LEFT JOIN sys.extended_properties ON (Syscolumns.Id = sys.extended_properties.major_id AND
 Syscolumns.Colid = sys.extended_properties.minor_id)
";
            strSQL += " WHERE sysobjects.name='" + tableName + "' and (sysobjects.Xtype ='u' OR Sysobjects.Xtype ='v') ";

            strSQL += @" AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType 
AND SysTypes.Name <> 'sysname' AND Sysobjects.Name Like '%' ORDER By  SysColumns.colid ";

            return strSQL;
        }
    }
}
